package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DATA.Book;
import DATA.Student;

public class SQLconnect {
	private final String driver = "com.mysql.jdbc.Driver";
	private final String URL = "jdbc:mysql://127.0.0.1:3306/work";
	private final String username = "root";
	private final String password = "yanzhiqiang0715";
	public Connection con = null;
	
	
	@SuppressWarnings("deprecation")
	public SQLconnect(){
		try {
			Class.forName(driver).newInstance();
			con = DriverManager.getConnection(URL,username,password);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			System.out.println("找不到数据库连接驱动！");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insertStudent(Student student) {
		String sqlstr="insert into student"+" values('"+student.getStudentnum()+"','"
	      +student.getName()+"','"+student.getStudentclass()+"','"+student.getGrade()+"','"+student.getSex()+"');";
		System.out.println(sqlstr);
        try {
			
			PreparedStatement pre = con.prepareStatement(sqlstr);
			pre.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insertBook(Book book) {
		String sqlstr="insert into book"+" values('"+book.getBooknum()+"','"+book.getBookname()+"','"+book.getAuthor()+"');";
		System.out.println(sqlstr);
        try {
			
			PreparedStatement pre = con.prepareStatement(sqlstr);
			pre.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}  
	}
	public List<Student> selectAllStudent(){
		String sqlstr ="select * from student where 1=1";
		try {
			PreparedStatement ps = con.prepareStatement(sqlstr);
			ResultSet rs = ps.executeQuery();
			List<Student> studentList = new ArrayList<Student>();
			while(rs.next()){
				Student student = new Student();
				student.setStudentnum(rs.getString(1));
				student.setName(rs.getString(2));
				student.setStudentclass(rs.getString(3));
				student.setGrade(rs.getInt(4));
				student.setSex(rs.getString(5));
				studentList.add(student);
				}
			return studentList;
			}catch (SQLException e) {
				System.out.println("查找无结果");
				e.printStackTrace();
				return null;
				}
		}
	public List<Book> selectallBook(){
		String sqlstr="select * from book where 1=1";
		try {
			PreparedStatement ps =con.prepareStatement(sqlstr);
		    ResultSet rs =ps.executeQuery();
			List<Book> bookList=new ArrayList<Book>();
			while(rs.next()) {
				Book book=new Book();
				book.setBooknum(rs.getString(1));
				book.setBookname(rs.getString(2));
				book.setAuthor(rs.getString(3));
				bookList.add(book);
			}
			return bookList;
		}catch (SQLException e) {
			System.out.println("查找无结果");
			e.printStackTrace();
			return null;
			}
		}
	}
